﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data; 
using System.Data.Common; 
using WMSADBUtility;
using WMSAML.SY;

namespace WMSADL.SY
{
    public class TRoles_DAL
    {
        /// <summary>
        /// 
        /// </summary>
        public int Add(TRoles_Entity entity)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("insert into TRoles(");
            strSql.Append("Parent_id,Role_name,Creat_time,Description)");

            strSql.Append(" values (");
            strSql.Append("@Parent_id,@Role_name,@Creat_time,@Description)");
            strSql.Append(";select @@IDENTITY");
            Database db = DatabaseFactory.CreateDatabase();
            DbCommand dbCommand = db.GetSqlStringCommand(strSql.ToString());
            db.AddInParameter(dbCommand, "Parent_id", DbType.Int32, entity.Parent_id);
            db.AddInParameter(dbCommand, "Role_name", DbType.String, entity.Role_name);
            db.AddInParameter(dbCommand, "Creat_time", DbType.DateTime, entity.Creat_time);
            db.AddInParameter(dbCommand, "Description", DbType.String, entity.Description);
            int result;
            object obj = db.ExecuteScalar(dbCommand);
            if (!int.TryParse(obj.ToString(), out result))
            {
                return 0;
            }
            return result;
        }

        /// <summary>
        /// 
        /// </summary>
        public bool Delete(int id)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("DELETE FROM TRoles where Role_id=@Role_id ");
            Database db = DatabaseFactory.CreateDatabase();
            using (DbCommand dbCommand = db.GetSqlStringCommand(strSql.ToString()))
            {
                db.AddInParameter(dbCommand, "Role_id", DbType.Int32, id);
                int rows = db.ExecuteNonQuery(dbCommand);

                if (rows > 0)
                {
                    return true;
                }
                else
                {
                    return false;
                }
            }
        }

        /// <summary>
        /// 更新一条数据
        /// </summary>
        public bool Update(TRoles_Entity entity)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update TRoles set ");
            strSql.Append("Parent_id=@Parent_id,");
            strSql.Append("Role_name=@Role_name,");
            strSql.Append("Creat_time=@Creat_time,");
            strSql.Append("Description=@Description");
            strSql.Append(" where Role_id=@Role_id ");
            Database db = DatabaseFactory.CreateDatabase();
            using (DbCommand dbCommand = db.GetSqlStringCommand(strSql.ToString()))
            {
                db.AddInParameter(dbCommand, "Role_id", DbType.Int32, entity.Role_id);
                db.AddInParameter(dbCommand, "Parent_id", DbType.Int32, entity.Parent_id);
                db.AddInParameter(dbCommand, "Role_name", DbType.String, entity.Role_name);
                db.AddInParameter(dbCommand, "Creat_time", DbType.DateTime, entity.Creat_time);
                db.AddInParameter(dbCommand, "Description", DbType.String, entity.Description);
                int rows = db.ExecuteNonQuery(dbCommand);

                if (rows > 0)
                {
                    return true;
                }
                else
                {
                    return false;
                }
            }
        }
        /// <summary>
        /// ByID
        /// </summary>
        public bool ExistsByID(int id)
        {
            Database db = DatabaseFactory.CreateDatabase();
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select count(1) from TRoles where Role_id=@Role_id ");
            using (DbCommand dbCommand = db.GetSqlStringCommand(strSql.ToString()))
            {
                db.AddInParameter(dbCommand, "Role_id", DbType.Int32, id);
                int cmdresult;
                object obj = db.ExecuteScalar(dbCommand);
                if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
                {
                    cmdresult = 0;
                }
                else
                {
                    cmdresult = int.Parse(obj.ToString());
                }
                if (cmdresult == 0)
                {
                    return false;
                }
                else
                {
                    return true;
                }
            }
        }

        /// <summary>
        /// ByName
        /// </summary>
        public bool ExistsByName(string role_name)
        {
            Database db = DatabaseFactory.CreateDatabase();
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select count(1) from TRoles where Role_name=@Role_name ");
            using (DbCommand dbCommand = db.GetSqlStringCommand(strSql.ToString()))
            {
                db.AddInParameter(dbCommand, "Role_name", DbType.String, role_name);
                int cmdresult;
                object obj = db.ExecuteScalar(dbCommand);
                if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
                {
                    cmdresult = 0;
                }
                else
                {
                    cmdresult = int.Parse(obj.ToString());
                }
                if (cmdresult == 0)
                {
                    return false;
                }
                else
                {
                    return true;
                }
            }
        }

        /// <summary>
        /// ByName
        /// </summary>
        /// <summary>
        public TRoles_Entity GetEntityByName(string role_name)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select Role_id,Parent_id,Role_name,Creat_time,Description from TRoles ");
            strSql.Append(" where Role_name=@Role_name ");
            Database db = DatabaseFactory.CreateDatabase();
            using (DbCommand dbCommand = db.GetSqlStringCommand(strSql.ToString()))
            {
                db.AddInParameter(dbCommand, "Role_name", DbType.String, role_name);
                TRoles_Entity entity = new TRoles_Entity();
                using (IDataReader dataReader = db.ExecuteReader(dbCommand))
                {
                    if (dataReader.Read())
                    {
                        entity.Role_id = Convert.ToInt32(dataReader["Role_id"].ToString());
                        entity.Parent_id = Convert.ToInt32(dataReader["Parent_id"].ToString());
                        entity.Role_name = dataReader["Role_name"].ToString();
                        entity.Creat_time = Convert.ToDateTime(dataReader["Creat_time"].ToString());
                        entity.Description = dataReader["Description"].ToString();
                    }
                }
                return entity;
            }
        }

        /// <summary>
        /// ByID
        /// </summary>
        /// <summary>
        public TRoles_Entity GetEntityByID(int id)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select Role_id,Parent_id,Role_name,Creat_time,Description from TRoles ");
            strSql.Append(" where Role_id=@Role_id ");
            Database db = DatabaseFactory.CreateDatabase();
            using (DbCommand dbCommand = db.GetSqlStringCommand(strSql.ToString()))
            {
                db.AddInParameter(dbCommand, "Role_id", DbType.Int32, id);
                TRoles_Entity entity = new TRoles_Entity();
                using (IDataReader dataReader = db.ExecuteReader(dbCommand))
                {
                    if (dataReader.Read())
                    {
                        entity.Role_id = Convert.ToInt32(dataReader["Role_id"].ToString());
                        entity.Parent_id = Convert.ToInt32(dataReader["Parent_id"].ToString());
                        entity.Role_name = dataReader["Role_name"].ToString();
                        entity.Creat_time = Convert.ToDateTime(dataReader["Creat_time"].ToString());
                        entity.Description = dataReader["Description"].ToString();
                    }
                }
                return entity;
            }
        }


        /// <summary>
        /// 获得数据列表
        /// </summary>
        public DataSet GetList_Roles()
        {
            Database db = DatabaseFactory.CreateDatabase();
            StringBuilder strSql = new StringBuilder();
            DataSet ds = new DataSet();
            strSql.Append("select Role_id,Parent_id,Role_name,Creat_time,Description from TRoles ");
            using (DbCommand dbCommand = db.GetSqlStringCommand(strSql.ToString()))
            {
                ds = db.ExecuteDataSet(dbCommand);
                return (ds.Tables[0].Rows.Count > 0) ? ds : null;
            }
        }

    }
}
